from sqlalchemy.orm import Session
from sqlalchemy import text
from datetime import datetime
from typing import List, Dict
import redis

def get_flow_stats_by_timeslot(db: Session, start_time: str, end_time: str):
    """
    根据给定的时间范围，从数据库中查询每个社区的借还车流量。
    """
    query = text("""
        WITH borrows AS (
            -- 计算每个起点社区的借车数量
            SELECT 
                start_community_id AS community_id,
                COUNT(*) AS borrow_count
            FROM bike_orders
            WHERE start_time >= :start_time AND start_time < :end_time AND start_community_id IS NOT NULL
            GROUP BY start_community_id
        ),
        returns AS (
            -- 计算每个终点社区的还车数量
            SELECT 
                end_community_id AS community_id,
                COUNT(*) AS return_count
            FROM bike_orders
            WHERE end_time >= :start_time AND end_time < :end_time AND end_community_id IS NOT NULL
            GROUP BY end_community_id
        )
        -- 将借车和还车数据与所有社区进行左连接，确保每个社区都有记录
        SELECT
            c.id AS community_id,
            COALESCE(b.borrow_count, 0) AS borrow_count,
            COALESCE(r.return_count, 0) AS return_count,
            (COALESCE(r.return_count, 0) - COALESCE(b.borrow_count, 0)) AS net_flow
        FROM communities c
        LEFT JOIN borrows b ON c.id = b.community_id
        LEFT JOIN returns r ON c.id = r.community_id
        ORDER BY c.id;
    """)
    
    result = db.execute(query, {"start_time": start_time, "end_time": end_time})
    
    return result.fetchall()


def get_od_pairs_by_timeslot(db: Session, start_time: str, end_time: str, min_flow: int = 1):
    """
    根据给定的时间范围，查询社区之间的 OD (起点-终点) 流动数据。
    """
    query = text("""
        SELECT
            start_community_id,
            end_community_id,
            COUNT(*) AS flow_count
        FROM
            bike_orders
        WHERE
            start_time >= :start_time 
            AND start_time < :end_time
            AND start_community_id IS NOT NULL
            AND end_community_id IS NOT NULL
            AND start_community_id != end_community_id -- 排除社区内部的流动
        GROUP BY
            start_community_id,
            end_community_id
        HAVING
            COUNT(*) >= :min_flow -- 只返回流量大于等于 min_flow 的路径
        ORDER BY
            flow_count DESC;
    """)
    
    result = db.execute(
        query, 
        {"start_time": start_time, "end_time": end_time, "min_flow": min_flow}
    )
    
    return result.fetchall()


def get_timeseries_by_community(db: Session, community_id: int, start_date: str, end_date: str):
    """
    查询单个社区在指定日期范围内的借还车时序数据。
    """
    query = text("""
        -- 1. 使用 generate_series 生成一个完整的时间槽序列 (例如 48 个)
        WITH time_slots AS (
            SELECT generate_series(
                -- 修复：使用 CAST 函数进行类型转换，避免语法歧义
                CAST(:start_date AS timestamp), 
                CAST(:end_date AS timestamp) - interval '30 minute', 
                '30 minute'
            ) AS time_slot
        ),
        -- 2. 计算该社区在每个时间槽的借车数
        borrows AS (
            SELECT
                date_trunc('hour', start_time) + floor(extract(minute from start_time) / 30) * interval '30 minute' AS time_slot,
                COUNT(*) as borrow_count
            FROM bike_orders
            WHERE start_community_id = :community_id AND start_time >= :start_date AND start_time < :end_date
            GROUP BY 1
        ),
        -- 3. 计算该社区在每个时间槽的还车数
        returns AS (
            SELECT
                date_trunc('hour', end_time) + floor(extract(minute from end_time) / 30) * interval '30 minute' AS time_slot,
                COUNT(*) as return_count
            FROM bike_orders
            WHERE end_community_id = :community_id AND end_time >= :start_date AND end_time < :end_date
            GROUP BY 1
        )
        -- 4. 将完整的时间槽序列与借车、还车数据左连接
        SELECT 
            ts.time_slot,
            COALESCE(b.borrow_count, 0) AS borrow_count,
            COALESCE(r.return_count, 0) AS return_count
        FROM time_slots ts
        LEFT JOIN borrows b ON ts.time_slot = b.time_slot
        LEFT JOIN returns r ON ts.time_slot = r.time_slot
        ORDER BY ts.time_slot;
    """)
    
    result = db.execute(
        query,
        {"community_id": community_id, "start_date": start_date, "end_date": end_date}
    )
    
    return result.fetchall()


def get_all_historical_flows_from_redis(redis_client: redis.Redis) -> List[Dict]:
    """
    从 Redis 中获取所有社区在所有时间槽的净流量数据。
    """
    all_flows = []
    # 遍历所有社区
    for community_id in range(1, 75):
        redis_key = f"community:{community_id}:netflow"
        # HGETALL 一次性获取一个社区的所有时间槽数据，效率很高
        community_data = redis_client.hgetall(redis_key)
        
        for time_str, net_flow_str in community_data.items():
            all_flows.append({
                "community_id": community_id,
                "time_slot": datetime.fromisoformat(time_str),
                "net_flow": int(net_flow_str)
            })
    return all_flows